package com.example.demo01.Impl.webImpl;

import com.example.demo01.Dao.webDao.UserStatisticsInfoDao;
import com.example.demo01.Entity.webEntity.UserStatisticsInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;

@Repository
public class UserStatisticsInfoDaoImpl implements UserStatisticsInfoDao {
    //自动装配
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据idnum获取基本统计信息
     * */
    @Override
    public UserStatisticsInfo getUserStatisticsInfoByIdNum(String idnum) {
        String sql="SELECT \n" +
                "\tuserinfo1.name,\n" +
                "\tTIMESTAMPDIFF(YEAR,str_to_date(userinfo1.birthtime, '%Y-%m-%d'),date_format(NOW(), '%Y-%m-%d')) as age,\n" +
                "\tuserinfo1.sex,\n"+
                "\tuserinfo1.occupation,\n" +
                "\tuserinfo1.phonenum,\n" +
                "\tsignedrecord.agentname,\n" +
                "\tuserinfo1.curaddress,\n" +
                "\tuserinfo1.nurselevel,\n" +
                "\tTIMESTAMPDIFF(DAY,str_to_date(signedrecord.signeddate, '%Y-%m-%d'),date_format(NOW(), '%Y-%m-%d')) as serdays,\n" +
                "\t(SELECT COUNT(*) FROM serviceorders,userinfo1 WHERE userinfo1.idnum=? AND serviceorders.clientusername=userinfo1.phonenum) as serTimes,\n" +
                "\t(SELECT COUNT(*) FROM warnrecord,userinfo1 WHERE userinfo1.idnum=? AND warnrecord.idnumber=userinfo1.idnum) as warnTimes,\n" +
                "\tuserinfo1.lon,\n" +
                "\tuserinfo1.lat\n" +
                "FROM \n" +
                "\tuserinfo1,signedrecord\n" +
                "WHERE \n" +
                "\tuserinfo1.idnum=? AND userinfo1.idnum=signedrecord.objidcard";
        return jdbcTemplate.queryForObject(sql, new Object[]{idnum, idnum, idnum}, new RowMapper<UserStatisticsInfo>() {
            @Nullable
            @Override
            public UserStatisticsInfo mapRow(ResultSet resultSet, int i) throws SQLException {
                return new UserStatisticsInfo(
                        resultSet.getString(1),//姓名
                        resultSet.getInt(2),//年龄
                        resultSet.getString(3),//性别
                        resultSet.getString(4),//职业
                        resultSet.getString(5),//手机号
                        resultSet.getString(6),//代理人姓名
                        resultSet.getString(7),//现住址
                        resultSet.getString(8),//护理等级
                        resultSet.getInt(9),//服务时长
                        resultSet.getInt(10),//服务次数
                        resultSet.getInt(11),//报警次数
                        resultSet.getFloat(12),//经度
                        resultSet.getFloat(13)//纬度
                );
            }
        });
    }
}
